﻿package top.college.cjh;

import java.util.ArrayList;
import java.util.List;

import model.AdminVO;
import model.DepartVO;
import model.ProfessorVO;
import model.StudentVO;
import model.SubjectVO;
import dao.JDBC_ConnClose;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import model.AdminVO;

import dao.JDBC_SQL;
import dao.JDBC_ConnClose;
import screen.AdminMain;
import screen.LoginMain;

public class Admin_Select {

static 	JDBC_SQL sql = new JDBC_SQL();
static StringBuilder sb = new StringBuilder();
static Scanner sc = new Scanner(System.in); 
static boolean b = true;
static String[] smt = { "순번", "ID", "이름", "연락처", "등록일자" };
static Connection conn = null;
static PreparedStatement pstmt = null;
static ResultSet rs = null;
static String add = null;
static String logId = LoginMain.logIdVO;
private static String selectSQL = "select * from ADMIN_INFO order by ADMIN_TR DESC";
private static String DepartSelectSQL = "SELECT * FROM DEPART order by DEPT_TR DESC";
private static String StuSelectSQL = "SELECT * FROM STUDENT order by STD_TR DESC";
private static String SubSelectSQL = "SELECT * FROM SUBJECT order by SUB_TR DESC";
private static String profSelectSQL = "SELECT * FROM PROFESSOR order by PROF_TR DESC";
	public static List<AdminVO> selectMember() {
		List<AdminVO> result = new ArrayList<>();	
			try {
					
				conn = JDBC_ConnClose.openDataBase();
				pstmt = conn.prepareStatement( selectSQL );
				rs = pstmt.executeQuery();
					// 동적배열
				while( rs.next() ) {
					AdminVO m = new AdminVO();
					m.setAdmin_tr( rs.getString("ADMIN_TR") );
					m.setAdmin_id( rs.getString("ADMIN_ID") );
					m.setAdmin_name( rs.getString("ADMIN_NAME") );
					m.setAdmin_pw( rs.getString("ADMIN_PW") );
					m.setAdmin_tel( rs.getString("ADMIN_TEL") );
					m.setReg_date( rs.getString("reg_date") );
					result.add( m );
				}
				
				
		} catch ( Exception ex ) {
			ex.printStackTrace();
			
		}finally {
		
			JDBC_ConnClose.closeDataBase(conn, pstmt, rs);
		}
		
		return result;
	}
	
	public static List<DepartVO> selectDepart() {
		List<DepartVO> result = new ArrayList<>();			// 동적배열
		
		try {
				conn = JDBC_ConnClose.openDataBase();
				pstmt = conn.prepareStatement(DepartSelectSQL);
				rs = pstmt.executeQuery();
				while( rs.next() ) {
					DepartVO d = new DepartVO();
					d.setDept_tr( rs.getString("DEPT_TR") );
					d.setDept_no( rs.getString("DEPT_NO") );
					d.setDept_name( rs.getString( "DEPT_NAME") );
					d.setDept_tel( rs.getString( "DEPT_TEL") );
					d.setDept_loc( rs.getString( "DEPT_LOC") );
					d.setProf_no( rs.getString("PROF_NO") );
					d.setReg_date( rs.getString("REG_DATE") );
					result.add( d );
										
				}
				
				
		} catch ( Exception ex ) {
			ex.printStackTrace();
			
		}finally {
		
			JDBC_ConnClose.closeDataBase(conn, pstmt, rs);
		}
		
		return result;
	}
	
	public static List<StudentVO> selectStu() {
		List<StudentVO> result = new ArrayList<>();			// 동적배열
		
		try {
				conn = JDBC_ConnClose.openDataBase();
				pstmt = conn.prepareStatement(StuSelectSQL);
				rs = pstmt.executeQuery();
				while( rs.next() ) {
					StudentVO s = new StudentVO();
					s.setStd_tr( rs.getString("STD_TR") );
					s.setStd_no( rs.getString("STD_NO") );
					s.setStd_pw( rs.getString( "STD_PW") );
					s.setStd_name( rs.getString( "STD_NAME") );
					s.setStd_tel( rs.getString( "STD_TEL") );
					s.setStd_addr( rs.getString("STD_ADDR") );
					s.setDept_no( rs.getString("DEPT_NO") );
					s.setTot_point( rs.getString("TOT_POINT") );
					s.setReg_date( rs.getString("REG_DATE") );
					
					result.add( s );
										
				}
				
				
		} catch ( Exception ex ) {
			ex.printStackTrace();
			
		}finally {
		
			JDBC_ConnClose.closeDataBase(conn, pstmt, rs);
		}
		
		return result;
	}
	
	public static List<SubjectVO> selectSub() {
		List<SubjectVO> result = new ArrayList<>();			// 동적배열
		
		try {
				conn = JDBC_ConnClose.openDataBase();
				pstmt = conn.prepareStatement(SubSelectSQL);
				rs = pstmt.executeQuery();
				
				while( rs.next() ) {
					
					SubjectVO s = new SubjectVO();
					
					s.setSub_tr( rs.getString("SUB_TR") );
					s.setSub_no( rs.getString( "SUB_NO") );
					s.setSub_name( rs.getString( "SUB_NAME") );
					s.setSub_type_no( rs.getString( "SUB_TYPE_NO") );
					s.setSub_info( rs.getString("SUB_INFO") );
					s.setSub_point( rs.getString( "SUB_POINT") );
					s.setProf_no( rs.getString("PROF_NO") );
					s.setReg_date( rs.getString("REG_DATE") );
					
					result.add( s );
										
				}
				
				
		} catch ( Exception ex ) {
			ex.printStackTrace();
			
		}finally {
		
			JDBC_ConnClose.closeDataBase(conn, pstmt, rs);
		}
		
		return result;
	}
	
	public static List<ProfessorVO> selectProf() {
		List<ProfessorVO> result = new ArrayList<>();			// 동적배열
		
		try {
				conn = JDBC_ConnClose.openDataBase();
				pstmt = conn.prepareStatement(profSelectSQL);
				rs = pstmt.executeQuery();
				
				while( rs.next() ) {
					
					ProfessorVO p = new ProfessorVO();				
					p.setProf_tr( rs.getString( "PROF_TR") );
					p.setProf_no( rs.getString( "PROF_NO") );
					p.setProf_pw( rs.getString( "PROF_PW") );
					p.setProf_name( rs.getString( "PROF_NAME") );
					p.setDept_no( rs.getString("DEPART_NO") );
					p.setProf_tel( rs.getString( "PROF_TEL") );
					p.setProf_addr( rs.getString("PROF_ADDR") );
					p.setProf_office( rs.getString("PROF_OFFICE") );
					p.setReg_date( rs.getString("REG_DATE") );
					
					result.add( p );
										
				}
				
				
		} catch ( Exception ex ) {
			ex.printStackTrace();
			
		}finally {
		
			JDBC_ConnClose.closeDataBase(conn, pstmt, rs);
		}
		
		return result;
	}
	
	
}// class
